package cn.soft863.users.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import cn.soft863.common.util.DbUtil;
import cn.soft863.common.util.PageInfoUtil;
import cn.soft863.users.dao.IRegionDao;
import cn.soft863.users.pojo.ARegion;


public class RegionDaoImp implements IRegionDao{

	@Override
	public int insert(ARegion aRegion) throws Exception {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO a_region (parentId, name, pinyin, areacode, content, leafStatus, status, createTime, updateTime, pubTime) VALUES "
				+ "	(?,?,?,?,?,?,?,?,?,?)"; 
		/* ����һ�����Ӷ��� */
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ; 
		
		int res = 0 ; 
		/* ����һ�������� */
		try
		{
			ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			/* ����ռλ�� */
			ps.setObject(1, aRegion.getParentId());
			ps.setObject(2, aRegion.getName());
			ps.setObject(3, aRegion.getPinyin());
			ps.setObject(4, aRegion.getAreacode());
			ps.setObject(5, aRegion.getContent());
			ps.setObject(6, aRegion.getLeafStatus());
			ps.setObject(7, aRegion.getStatus());
			ps.setObject(8, aRegion.getCreateTime());
			ps.setObject(9, aRegion.getUpdateTime());
			ps.setObject(10, aRegion.getPubTime());
			res = ps.executeUpdate();
			/* res��ʾ����sql���Ӱ������
			 * ��Ҫ��������
			 *  */
			rs = ps.getGeneratedKeys();
			/* �϶��н��,����ֻ��һ�� */
			if(rs.next())
			{
				/* ������ֵ */
				aRegion.setId(rs.getInt(1));
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			/* �ر����� */
			DbUtil.closeAll(rs, ps, con);
		}
		return res;
	}

	@Override
	public int updateOneDao(ARegion aRegion) throws Exception {
		// TODO Auto-generated method stub
		String sql = "update a_region set parentId=?, name=?, pinyin=?, areacode=?, "
				+ "content=?, leafStatus=?, status=?, createTime=?, updateTime=?,pubTime=? WHERE (id=?)"; 
		/* ����һ�����Ӷ��� */
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null ; 
		
		int res = 0 ; 
		/* ����һ�������� */
		try
		{
			ps = con.prepareStatement(sql);
			/* ����ռλ�� */
			ps.setObject(1, aRegion.getParentId());
			ps.setObject(2, aRegion.getName());
			ps.setObject(3, aRegion.getPinyin());
			ps.setObject(4, aRegion.getAreacode());
			ps.setObject(5, aRegion.getContent());
			ps.setObject(6, aRegion.getLeafStatus());
			ps.setObject(7, aRegion.getStatus());
			ps.setObject(8, aRegion.getCreateTime());
			ps.setObject(9, aRegion.getUpdateTime());
			ps.setObject(10, aRegion.getPubTime());
			ps.setObject(11, aRegion.getId());
			res = ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			/* �ر����� */
			DbUtil.closeAll(rs, ps, con);
		}
		return res;
	}

	@Override
	public List<ARegion> FindByOnes(PageInfoUtil page,Map<String,Object> map) throws SQLException
	{// TODO Auto-generated method stub
		String sql="select * from a_region where 1=1";
		String sql1="select count(*) from a_region where 1=1";
		List<Object> list=new ArrayList<Object>();
		if(map.get("keyword") != null && !"".equalsIgnoreCase(map.get("keyword") + ""))
		{
			sql += " and (email like ? or phone like ?) ";
			sql1 += " and (email like ? or phone like ?) ";
			list.add("%" + map.get("keyword") + "%");
			list.add("%" + map.get("keyword") + "%");
			/*
			 * select * from a_admins email like ? or phone like ?
			 *  */
		}
		/* ����״̬���� */
		if(map.get("status") != null && !"".equalsIgnoreCase(map.get("status") + ""))
		{
			sql += " and status = ? ";
			sql1 += " and status = ? ";
			list.add(map.get("status"));
			/*
			 * select * from a_admins status = ?
			 *  */
		}
		/* ����ʱ������ */
		if(map.get("stDate") != null && !"".equalsIgnoreCase(map.get("edDate") + ""))
		{
			sql += " and (createTime >= ? and createTime < ? )";
			sql1 += " and (createTime >= ? and createTime < ? )";
			list.add(map.get("stDate"));
			list.add(map.get("edDate"));
			
		}
		Connection con=null;
		PreparedStatement ps=null;
		con=DbUtil.getConnection();
		ps=con.prepareStatement(sql1);
		int count = 1 ; 
		for (Iterator iterator = list.iterator(); iterator.hasNext();)
		{
			Object obj = (Object) iterator.next();
			ps.setObject(count, obj);
			count ++ ; 
		}
//		System.out.println(count);
		ResultSet rs1=ps.executeQuery();
		while(rs1.next())
		{
			page.setTotalRecord(rs1.getInt(1));
		}
		DbUtil.closeAll(rs1, ps, con);
		sql+=" limit ?,?";
		System.out.println(sql);
		con=DbUtil.getConnection();
		 ps=con.prepareStatement(sql);
		
		list.add(page.getCurrRecord());
		list.add(page.getPageSize());
		int count1 = 1 ; 
		for (Iterator iterator = list.iterator(); iterator.hasNext();)
		{
			Object obj = (Object) iterator.next();
			ps.setObject(count1, obj);
			count1 ++ ; 
		}
		
		List<ARegion> listRegion=new ArrayList<ARegion>();
		rs1=ps.executeQuery();
		ARegion reg=new ARegion();
		while(rs1.next())
		{
			reg.setId(rs1.getInt(1));
			reg.setParentId(rs1.getInt(2));
			reg.setName(rs1.getString(3));
			reg.setPinyin(rs1.getString(4));
			reg.setAreacode(rs1.getString(5));
			reg.setContent(rs1.getString(6));
			reg.setLeafStatus(rs1.getByte(7));
			reg.setStatus(rs1.getByte(8));
			reg.setCreateTime(rs1.getDate(9));
			reg.setUpdateTime(rs1.getDate(10));
			reg.setPubTime(rs1.getDate(11));
			
			listRegion.add(reg);
		}
		DbUtil.closeAll(rs1, ps, con);
		
		return listRegion;
	}

	

}
